package com.example.outdoor;

import java.util.ArrayList;
import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


	public class myDBForCarrier extends SQLiteOpenHelper {
		private static final int DATABASE_VERSION = 1;
		 
	    // Database Name
	    private static final String DATABASE_NAME = "OutdoorAppDB.db";
	 
	    // Table Name
	    private static final String TABLE_MEMBER = "tblBillboard";

		public myDBForCarrier(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			// Create Table Name
			 db.execSQL("CREATE TABLE IF NOT EXISTS tblBillboard(ID INTEGER PRIMARY KEY , Avenue INTEGER,Date VARCHAR,Starttime VARCHAR,Endtime VARCHAR,Size VARCHAR,Carrier VARCHAR,CarrierDescription VARCHAR,Quality VARCHAR,RealCost VARCHAR,LatandLong VARCHAR,Numberofads VARCHAR,Totaladsinthatlocation VARCHAR,typeofads VARCHAR," +
	        		 "Visibilitycode VARCHAR,Visibilitydescription VARCHAR,Verificationdate VARCHAR,Verificationdescription VARCHAR,Versiocode VARCHAR,Versiondescription VARCHAR,Productcode VARCHAR,Constant VARCHAR,CategoryCode VARCHAR,Categorydescription VARCHAR,Subsectorcode VARCHAR,Subsectordescription VARCHAR,Sectorcode VARCHAR," +
	        		 "Sectordescription VARCHAR,Betweenavenue VARCHAR,Brandcode VARCHAR,Branddescription VARCHAR,Announcercode VARCHAR,Announcerdescription VARCHAR,Imagecode VARCHAR,Agencycode VARCHAR,Agencyname VARCHAR);");
			   
		   
		    Log.d("CREATE TABLE","Create Table Successfully.");
		}
		

		// Select Data
		public String[] SelectData(String strMemberID) {
			// TODO Auto-generated method stub
			
			 try {
				String arrData[] = null;	
				
				 SQLiteDatabase db;
				 db = this.getReadableDatabase(); // Read Data
					
				 Cursor cursor = db.query(TABLE_MEMBER, new String[] { "*" }, 
						 	"ID=?",
				            new String[] { String.valueOf(strMemberID) }, null, null, null, null);
				 
				 	if(cursor != null)
				 	{
						if (cursor.moveToFirst()) {
							arrData = new String[cursor.getColumnCount()];
							/***
							 *  0 = MemberID
							 *  1 = Name
							 *  2 = Tel
							 */
							arrData[0] = cursor.getString(0);
							arrData[1] = cursor.getString(6);
							arrData[2] = cursor.getString(5);
							arrData[3] = cursor.getString(7).toString().replace("\"", "");
						}
				 	}
				 	cursor.close();
				 	db.close();
					return arrData;
					
			 } catch (Exception e) {
			    return null;
			 }

		}
		
		
		// Show All Data
		public ArrayList<HashMap<String, String>> SelectAllData() {
			// TODO Auto-generated method stub
			
			 try {
				 
				 ArrayList<HashMap<String, String>> MyArrList = new ArrayList<HashMap<String, String>>();
				 HashMap<String, String> map;
				 
				 SQLiteDatabase db;
				 db = this.getReadableDatabase(); // Read Data
					
				 String strSQL = "SELECT  * FROM " + TABLE_MEMBER;
				 Cursor cursor = db.rawQuery(strSQL, null);
				 
				 	if(cursor != null)
				 	{
				 	    if (cursor.moveToFirst()) {
				 	        do {
				 	        	map = new HashMap<String, String>();
				 	        	map.put("ID", cursor.getString(0));
					 	        map.put("Carrier", cursor.getString(6));
					 	       map.put("Size", cursor.getString(5));
					 	        map.put("CarrierDescription", cursor.getString(7).toString().replace("\"", ""));
					 	        map.put("Image", cursor.getString(33));
					 	        MyArrList.add(map);
				 	        } while (cursor.moveToNext());
				 	    }
				 	}
				 	cursor.close();
				 	db.close();
					return MyArrList;
					
			 } catch (Exception e) {
			    return null;
			 }

		}
		
		// Update Data
		public long UpdateData(String strMemberID,String strName,String strTel,String strCarrierDescription) {
			// TODO Auto-generated method stub
			
			 try {
				
				SQLiteDatabase db;
	     		db = this.getWritableDatabase(); // Write Data
	     		
	     		/**
	     		 *  for API 11 and above
				SQLiteStatement insertCmd;
				String strSQL = "UPDATE " + TABLE_MEMBER
						+ " SET Name = ? "
						+ " , Tel = ? "
						+ " WHERE MemberID = ? ";
				
				insertCmd = db.compileStatement(strSQL);
				insertCmd.bindString(1, strName);
				insertCmd.bindString(2, strTel);
				insertCmd.bindString(3, strMemberID);
					
				return insertCmd.executeUpdateDelete();
				*
				*/
	            ContentValues Val = new ContentValues();
	            Val.put("Carrier", strName);
	            Val.put("Size", strTel);
	            Val.put("CarrierDescription", strCarrierDescription);
	            long rows = db.update(TABLE_MEMBER, Val, " ID = ?",
	                    new String[] { String.valueOf(strMemberID) });
	            
	     		db.close();
	     		return rows; // return rows updated.
					
			 } catch (Exception e) {
			    return -1;
			 }

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
	        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MEMBER);
	        
	        // Re Create on method  onCreate
	        onCreate(db);
		}
	}
